package com.studentstreet.tools.dbhelper;

import java.lang.reflect.Type;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.google.gson.Gson;
import com.google.gson.reflect.TypeToken;
import com.studentstreet.entities.CartEntity;
import com.studentstreet.entities.OrderEntity;
import com.studentstreet.entities.OrderItemEntity;
import com.studentstreet.tools.DES;

/**
 * @author likebamboo
 * 
 */
public class OrderDBHelper {
	private DBHelper dbHelper = null;
	// 订单详情描述，当要提交订单式生成
	public static String ORDETAIL = "";

	// public static String CART_ALL = "%";

	public OrderDBHelper(Context context) {
		super();
		dbHelper = new DBHelper(context);
	}

	// 将购物车数据保存
	public long saveOrderItem(int goodsId, int sellerId, String userName) {
		long row = 0L;
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		ContentValues values = new ContentValues();
		values.put(DBTools.ORDER_ITEM_ADDTIME, sdf.format(new Date()));
		values.put(DBTools.ORDER_ITEM_COUNT, 1);
		values.put(DBTools.ORDER_ITEM_GID, goodsId);
		values.put(DBTools.ORDER_ITEM_ISCOMMENT, 0);
		values.put(DBTools.ORDER_ITEM_ISPAID, 0);
		values.put(DBTools.ORDER_ITEM_SID, sellerId);
		values.put(DBTools.ORDER_ITEM_STATE, 0);
		values.put(DBTools.ORDER_ITEM_UNAME, userName);
		row = db.insert(DBTools.TABLE_ORDER_ITEM, null, values);
		db.close();
		return row;
	}

	// 得到订单总数量
	public int getCountTotal(String userName) {
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cursor = db.rawQuery("select sum(" + DBTools.ORDER_ITEM_COUNT
				+ ") from " + DBTools.TABLE_ORDER_ITEM + " where "
				+ DBTools.ORDER_ITEM_STATE + "=0 and "
				+ DBTools.ORDER_ITEM_UNAME + " like '" + userName + "'", null);
		int count = 0;
		while (cursor.moveToNext()) {
			count = cursor.getInt(0);
		}
		cursor.close();
		db.close();
		return count;
	}

	// 删除订单项
	public int delete(int goodsId, String userName) {
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		int result = db.delete(DBTools.TABLE_ORDER_ITEM, DBTools.ORDER_ITEM_GID
				+ " = " + goodsId + " and " + DBTools.ORDER_ITEM_UNAME
				+ " like '" + userName + "'", null);
		db.close();
		// System.out.println(result + "========result");
		return result;
	}

	// 获得总价格
	public double getPriceTotal(String userName) {
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cursor = db.rawQuery("select sum(" + DBTools.GOODS_PRICE + "*"
				+ DBTools.ORDER_ITEM_COUNT + ") from "
				+ DBTools.TABLE_ORDER_ITEM + " , " + DBTools.TABLE_GOODS
				+ " where " + DBTools.TABLE_GOODS + "." + DBTools.GOODS_ID
				+ " = " + DBTools.ORDER_ITEM_GID + " and "
				+ DBTools.ORDER_ITEM_STATE + "=0  and "
				+ DBTools.ORDER_ITEM_UNAME + " like '" + userName + "'", null);
		double price = 0;
		while (cursor.moveToNext()) {
			price = cursor.getInt(0);
		}
		cursor.close();
		db.close();
		return price;
	}

	// 数量增加
	public void updateUp(int id, String userName) {
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		db.execSQL("update " + DBTools.TABLE_ORDER_ITEM + " set "
				+ DBTools.ORDER_ITEM_COUNT + " = " + DBTools.ORDER_ITEM_COUNT
				+ " +1 where " + DBTools.ORDER_ITEM_GID + " = " + id + " and "
				+ DBTools.ORDER_ITEM_UNAME + " like '" + userName + "'");
		// System.out.println("update========up");
		db.close();
	}

	// 数量减少
	public void updateDown(int id, String userName) {
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		db.execSQL("update " + DBTools.TABLE_ORDER_ITEM + " set "
				+ DBTools.ORDER_ITEM_COUNT + " = " + DBTools.ORDER_ITEM_COUNT
				+ " -1 where " + DBTools.ORDER_ITEM_GID + " = " + id + " and "
				+ DBTools.ORDER_ITEM_COUNT + " > 1" + " and "
				+ DBTools.ORDER_ITEM_UNAME + " like '" + userName + "'");
		// System.out.println("update========down");
		db.close();
	}

	// 获得某个订单项的数量
	public int getCount(int id, String userName) {
		int count = 0;
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cursor = db
				.query(DBTools.TABLE_ORDER_ITEM, null, DBTools.ORDER_ITEM_GID
						+ " = " + id + " and " + DBTools.ORDER_ITEM_UNAME
						+ " like '" + userName + "'", null, null, null, null);
		cursor.moveToFirst();
		count = cursor.getInt(cursor.getColumnIndex(DBTools.ORDER_ITEM_COUNT));
		// System.out.println(id + "<-------id,count------->" + count);
		db.close();
		return count;
	}

	// 查询用户购物车数据中是否有某一商品
	public int selectOne(int goodsId, String userName) {
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cursor = db.query(DBTools.TABLE_ORDER_ITEM, null,
				DBTools.ORDER_ITEM_GID + "=? and " + DBTools.ORDER_ITEM_STATE
						+ "=0 and " + DBTools.ORDER_ITEM_UNAME + " like ? ",
				new String[] { goodsId + "", userName }, null, null, null);
		int rs = cursor.getCount();
		cursor.close();
		db.close();
		return rs;
	}

	// 从数据库中查询订单项数据并填充到CartEntity
	public List<CartEntity> getCart(String user) {
		List<CartEntity> cartList = new ArrayList<CartEntity>();
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		String sql = "                           " + "select  *  from "
				+ DBTools.TABLE_ORDER_ITEM + " ,  " + DBTools.TABLE_GOODS
				+ "  ,  " + DBTools.TABLE_SELLER + " where "
				+ DBTools.TABLE_ORDER_ITEM + "." + DBTools.ORDER_ITEM_GID
				+ " = " + DBTools.TABLE_GOODS + "." + DBTools.GOODS_ID
				+ "  and  " + DBTools.TABLE_GOODS + "." + DBTools.GOODS_SID
				+ "  = " + DBTools.TABLE_SELLER + "." + DBTools.SELLER_ID
				+ "  and " + DBTools.TABLE_ORDER_ITEM + "."
				+ DBTools.ORDER_ITEM_UNAME + " like '" + user + "' and "
				+ DBTools.ORDER_ITEM_STATE + " = 0";

		/*
		 * the sql statement is like this:---------------------------------->
		 * select * from order_item , goods , seller where order_item.goodsId =
		 * goods._id and goods.sellerId = seller._id and order_item.userName
		 * like '--userName--' and item_state=0;
		 */

		Cursor cursor = db.rawQuery(sql, null);
		while (cursor.moveToNext()) {
			CartEntity cart = new CartEntity();
			cart.setCart_name(cursor.getString(cursor
					.getColumnIndex(DBTools.GOODS_NAME)));
			cart.setCart_price(cursor.getDouble(cursor
					.getColumnIndex(DBTools.GOODS_PRICE)));
			cart.setCart_count(cursor.getInt(cursor
					.getColumnIndex(DBTools.ORDER_ITEM_COUNT)));
			cart.setCart_id(cursor.getInt(cursor
					.getColumnIndex(DBTools.ORDER_ITEM_GID)));
			cart.setCart_sellerName(cursor.getString(cursor
					.getColumnIndex(DBTools.SELLER_NAME)));
			cartList.add(cart);
		}
		cursor.close();
		db.close();
		// System.out.println(cartList);
		return cartList;
	}

	// 从数据库中查询订单项
	private List<OrderItemEntity> getOrderItemList(String userName) {
		List<OrderItemEntity> orderList = new ArrayList<OrderItemEntity>();
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cursor = db.query(DBTools.TABLE_ORDER_ITEM, null,
				DBTools.ORDER_ITEM_STATE + "=0 and " + DBTools.ORDER_ITEM_UNAME
						+ " like '" + userName + "' ", null, null, null, null);

		/*
		 * the sql statement is like this:---------------------------------->
		 * select * from order_item where item_state = 0 and item_userName like
		 * '--userName--';
		 */

		while (cursor.moveToNext()) {
			OrderItemEntity orderItem = new OrderItemEntity();
			orderItem.setAddTime(cursor.getString(cursor
					.getColumnIndex(DBTools.ORDER_ITEM_ADDTIME)));
			orderItem.setCount(cursor.getInt(cursor
					.getColumnIndex(DBTools.ORDER_ITEM_COUNT)));
			orderItem.setGoodsId(cursor.getInt(cursor
					.getColumnIndex(DBTools.ORDER_ITEM_GID)));
			orderItem.setId(cursor.getInt(cursor
					.getColumnIndex(DBTools.ORDER_ITEM_ID)));
			orderItem.setIsComment(cursor.getInt(cursor
					.getColumnIndex(DBTools.ORDER_ITEM_ISCOMMENT)));
			orderItem.setSellerId(cursor.getInt(cursor
					.getColumnIndex(DBTools.ORDER_ITEM_SID)));
			orderItem.setIspaid(cursor.getInt(cursor
					.getColumnIndex(DBTools.ORDER_ITEM_ISPAID)));
			orderItem.setOrderNo(cursor.getString(cursor
					.getColumnIndex(DBTools.ORDER_ITEM_NO)));
			orderItem.setState(cursor.getInt(cursor
					.getColumnIndex(DBTools.ORDER_ITEM_STATE)));
			orderList.add(orderItem);
		}
		// 生成订单详情描述，用于插入order表
		OrderDBHelper.ORDETAIL = toOrderDetailString(orderList);
		cursor.close();
		db.close();
		return orderList;
	}

	// 将订单数据转化为加密的Json字符串
	public String getOrderJson(String userName) {
		// 先获得数据库中的订单数据
		List<OrderItemEntity> orderList = getOrderItemList(userName);
		// 将订单数据转化为Json字符串
		Type listType = new TypeToken<ArrayList<OrderItemEntity>>() {
		}.getType();
		Gson gson = new Gson();
		String orderJson = gson.toJson(orderList, listType);
		// DES加密
		try {
			orderJson = DES.encryptDES(orderJson, DES.DEFAULT_KEY);
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("error");
		}
		return orderJson;
	}

	// 根据订单项列表生成订单描述(不用商品名了，太麻烦,反正客户端order表 order_detail字段没什么用)
	private static String toOrderDetailString(List<OrderItemEntity> orderList) {
		String orderInfo = "";
		for (OrderItemEntity order : orderList) {
			orderInfo += order.getGoodsId() + "(" + order.getCount() + " 份),";
		}
		return orderInfo.substring(0, orderInfo.length() - 1);
	}

	// 将服务器回复的订单数据保存到 order 表，并更新order_item表的订单号
	public long saveOrder(OrderEntity order, String userName) {
		long row = 0;
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		ContentValues values = new ContentValues();
		// id自动增长
		// values.put(DBTools.ORDER_ID, order.getId());
		values.put(DBTools.ORDER_DETAIL, order.getDetail());
		values.put(DBTools.ORDER_NO, order.getNo());
		values.put(DBTools.ORDER_SID, order.getSellerId());
		values.put(DBTools.ORDER_TO_ADDR, order.getToAddr());
		values.put(DBTools.ORDER_TO_USER, order.getToUser());
		values.put(DBTools.ORDER_TO_TIME, order.getToTime());
		values.put(DBTools.ORDER_STATE, order.getState());
		values.put(DBTools.ORDER_ADD_TIME, order.getTime());
		values.put(DBTools.ORDER_UNAME, userName);
		row = db.insert(DBTools.TABLE_ORDER, null, values);

		db.close();
		insertOrderNo(order.getNo(), userName);
		return row;
	}

	// 更新订单号 至表 order_item
	public long insertOrderNo(String orderNo, String userName) {
		long row = 0;
		System.out.println("userName==============>" + userName);
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		ContentValues values = new ContentValues();
		values.put(DBTools.ORDER_ITEM_NO, orderNo);
		row = db.update(DBTools.TABLE_ORDER_ITEM, values,
				DBTools.ORDER_ITEM_STATE + " = 0 and "
						+ DBTools.ORDER_ITEM_UNAME + " like '" + userName
						+ "' ", null);
		/*
		 * the sql statement is like this:---------------------------------->
		 * update order_item set item_orderNo='--orderNo--' where item_state = 0
		 * and item_userName like '--userName--';
		 */
		db.close();
		return row;
	}

	// 查询是否有未提交的订单
	public boolean haveNotCommit(String userName) {
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cursor = db.query(DBTools.TABLE_ORDER_ITEM, null,
				DBTools.ORDER_ITEM_STATE + "= 0 and "
						+ DBTools.ORDER_ITEM_UNAME + " like '" + userName
						+ "' ", null, null, null, null);
		/*
		 * the sql statement is like this:---------------------------------->
		 * select * from order_item where item_state = 0 and item_userName like
		 * '--userName--';
		 */
		int count = cursor.getCount();
		System.out.println("count-------->" + count);
		cursor.close();
		db.close();
		return count > 0;
	}

	// 修改订单项的状态
	public void setOrderState(String orderNo, String userName) {
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		String sql = "update " + DBTools.TABLE_ORDER_ITEM + " set "
				+ DBTools.ORDER_ITEM_STATE + " = " + DBTools.ORDER_ITEM_STATE
				+ " +1 where " + DBTools.ORDER_ITEM_UNAME + " like '"
				+ userName + "' ";
		/*
		 * the sql statement is like this: ---------------------------------- >
		 * update order_item set item_state = item_state +1 where item_userName
		 * like '--userName--' ";
		 */
		db.execSQL(sql);
		db.close();
	}

	// OEM++ 2013-1-15 获得订单项的数量
	public int getItemCount(String userName) {
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cursor = db.rawQuery("select count(*) from "
				+ DBTools.TABLE_ORDER_ITEM + " where "
				+ DBTools.ORDER_ITEM_STATE + "=0 and "
				+ DBTools.ORDER_ITEM_UNAME + " like '" + userName + "'", null);
		int count = 0;
		while (cursor.moveToNext()) {
			count = cursor.getInt(0);
		}
		cursor.close();
		db.close();
		return count;
	}
	// OEM--
}
